﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using IndoReputation.Models.BusinessObject;
using System.Data.SqlClient;
using System.Data;
using IndoReputation.Models.BusinessObject.Collections;

namespace IndoReputation.Models.DataAccess
{
    public class NewsDAL
    {
        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/30/2011  9:39 AM
        /// Todo: Insert a Object User to Database
        /// </summary>
        /// <param name="news">A user.</param>
        /// <returns></returns>
        public static Int64 Insert(News news)
        {
            try
            {
                string spName = "News_Insert";
                SqlParameter[] spPara = new SqlParameter[14];
                spPara[0] = new SqlParameter("@ID", SqlDbType.Int, 2, ParameterDirection.Output, false, 0, 0, null, DataRowVersion.Default, null);
                spPara[1] = new SqlParameter("@NewsCategory", news.NewsCategory);
                spPara[2] = new SqlParameter("@NewsCategoryName", news.NewsCategoryName);
                spPara[3] = new SqlParameter("@MediaID", news.MediaID);
                spPara[4] = new SqlParameter("@MediaName", news.MediaName);
                spPara[5] = new SqlParameter("@DatePublished", news.DatePublished);
                spPara[6] = new SqlParameter("@Title", news.Title);
                spPara[7] = new SqlParameter("@Sumary", news.Sumary);
                spPara[8] = new SqlParameter("@Link", news.Link);
                spPara[9] = new SqlParameter("@CreateAt", news.CreateAt);
                spPara[10] = new SqlParameter("@CreateBy", news.CreateBy);
                spPara[11] = new SqlParameter("@UpdateAt", DBNull.Value);
                spPara[12] = new SqlParameter("@UpdateBy", DBNull.Value);
                spPara[13] = new SqlParameter("@IsPublished", news.IsPublished);
                DataAccessBase.ExecuteNonQuery(CommandType.StoredProcedure, spName, spPara);
                return Convert.ToInt64(spPara[0].Value);
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/30/2011  9:45 AM
        /// Todo: Update a User Exists in Database 
        /// </summary>
        /// <param name="news">A user.</param>
        /// <returns></returns>
        public static bool Update(News news)
        {
            try
            {
                string spName = "News_UpdateByPK";
                SqlParameter[] spPara = new SqlParameter[14];
                spPara[0] = new SqlParameter("@ID", news.ID);
                spPara[1] = new SqlParameter("@NewsCategory", news.NewsCategory);
                spPara[2] = new SqlParameter("@NewsCategoryName", news.NewsCategoryName);
                spPara[3] = new SqlParameter("@MediaID", news.MediaID);
                spPara[4] = new SqlParameter("@MediaName", news.MediaName);
                spPara[5] = new SqlParameter("@DatePublished", news.DatePublished);
                spPara[6] = new SqlParameter("@Title", news.Title);
                spPara[7] = new SqlParameter("@Sumary", news.Sumary);
                spPara[8] = new SqlParameter("@Link", news.Link);
                spPara[9] = new SqlParameter("@CreateAt", news.CreateAt);
                spPara[10] = new SqlParameter("@CreateBy", news.CreateBy);
                spPara[11] = new SqlParameter("@UpdateAt", news.UpdateAt);
                spPara[12] = new SqlParameter("@UpdateBy", news.UpdateBy);
                spPara[13] = new SqlParameter("@IsPublished", news.IsPublished);
                int totalRecordEffected = 0;
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                totalRecordEffected = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                            }
                        }
                    }
                }
                if (totalRecordEffected > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception Ex)
            {
                return false;
                throw Ex;
            }
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/30/2011  10:36 AM
        /// Todo: Delete a User Existst in Database
        /// </summary>
        /// <param name="user">A user.</param>
        /// <returns></returns>
        public static bool Delete(int newsID)
        {
            try
            {
                string spName = "News_DeleteByPK";
                SqlParameter[] spPara = new SqlParameter[1];
                spPara[0] = new SqlParameter("@ID", newsID);
                int totalRecordEffected = 0;
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                totalRecordEffected = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                            }
                        }
                    }
                }
                if (totalRecordEffected > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception Ex)
            {
                return false;
                throw Ex;
            }
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/29/2011  5:23 PM
        /// Todo: Get All Users
        /// </summary>
        /// <returns></returns>
        public static NewsList GetAllNews()
        {
            try
            {
                NewsList listNews = null;
                string spName = "News_SelectAll";
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, null);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                listNews = new NewsList();
                                List<News> listTemp = DataAccessBase.ConvertToList<News>(ds.Tables[0]);
                                foreach (News _r in listTemp)
                                    listNews.Add(_r);
                            }
                        }
                    }
                }
                return listNews;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/30/2011  10:40 AM
        /// Todo: Select a User existst in Database
        /// </summary>
        /// <param name="newsID">The user ID.</param>
        /// <returns></returns>
        public static News GetNewsById(int newsID)
        {
            try
            {
                News news = null;
                string spName = "News_SelectByPK";
                SqlParameter[] spPara = new SqlParameter[1];
                spPara[0] = new SqlParameter("@ID", newsID);
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                news = DataAccessBase.ConvertToEntity<News>(ds.Tables[0].Rows[0]);
                            }
                        }
                    }
                }
                return news;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        public static NewsJoinList GetAllNewsJoin()
        {
            try
            {
                NewsJoinList listNews = null;
                string spName = "News_SelectAllJoin";
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, null);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                listNews = new NewsJoinList();
                                List<NewsJoin> listTemp = DataAccessBase.ConvertToList<NewsJoin>(ds.Tables[0]);
                                foreach (NewsJoin _r in listTemp)
                                    listNews.Add(_r);
                            }
                        }
                    }
                }
                return listNews;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }        

        public static Int64 GetCountFromTo(DateTime dtFrom, DateTime dtTo)
        {
            try
            {
                Int64 result = 0;
                string spName = "News_SelectCountFromTo";
                SqlParameter[] spPara = new SqlParameter[2];
                spPara[0] = new SqlParameter("@dateFrom", dtFrom);
                spPara[1] = new SqlParameter("@dateTo", dtTo);                
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                result = Convert.ToInt64(ds.Tables[0].Rows[0]["Total"]);
                            }
                        }
                    }
                }
                return result;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        public static NewsJoinList GetTopNLastestNews(int top)
        {
            try
            {
                NewsJoinList listNews = null;
                string spName = "News_SelectTopnAllJoin";
                SqlParameter[] spPara = new SqlParameter[1];
                spPara[0] = new SqlParameter("@top", top);
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                listNews = new NewsJoinList();
                                List<NewsJoin> listTemp = DataAccessBase.ConvertToList<NewsJoin>(ds.Tables[0]);
                                foreach (NewsJoin _r in listTemp)
                                    listNews.Add(_r);
                            }
                        }
                    }
                }
                return listNews;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        public static Int64 GetCountChart(DateTime dtFrom, DateTime dtTo, int newsCategoryID)
        {
            try
            {
                Int64 result = 0;
                string spName = "News_SelectCountChart";
                SqlParameter[] spPara = new SqlParameter[3];
                spPara[0] = new SqlParameter("@dateFrom", dtFrom);
                spPara[1] = new SqlParameter("@dateTo", dtTo);
                spPara[2] = new SqlParameter("@categoryID", newsCategoryID);
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                result = Convert.ToInt64(ds.Tables[0].Rows[0]["Total"]);
                            }
                        }
                    }
                }
                return result;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        public static Int64 GetCountChartCountry(DateTime dtFrom, DateTime dtTo, int newsCategoryID, int countryID)
        {
            try
            {
                Int64 result = 0;
                string spName = "News_SelectCountChartCountry";
                SqlParameter[] spPara = new SqlParameter[4];
                spPara[0] = new SqlParameter("@dateFrom", dtFrom);
                spPara[1] = new SqlParameter("@dateTo", dtTo);
                spPara[2] = new SqlParameter("@categoryID", newsCategoryID);
                spPara[3] = new SqlParameter("@countryID", countryID);
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                result = Convert.ToInt64(ds.Tables[0].Rows[0]["Total"]);
                            }
                        }
                    }
                }
                return result;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        public static Int64 GetCountChartRegion(DateTime dtFrom, DateTime dtTo, int newsCategoryID, int regionID)
        {
            try
            {
                Int64 result = 0;
                string spName = "News_SelectCountChartRegion";
                SqlParameter[] spPara = new SqlParameter[4];
                spPara[0] = new SqlParameter("@dateFrom", dtFrom);
                spPara[1] = new SqlParameter("@dateTo", dtTo);
                spPara[2] = new SqlParameter("@categoryID", newsCategoryID);
                spPara[3] = new SqlParameter("@regionID", regionID);
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                result = Convert.ToInt64(ds.Tables[0].Rows[0]["Total"]);
                            }
                        }
                    }
                }
                return result;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        public static ChartShort GetTotalAndAvg(DateTime dtFrom, DateTime dtTo, int newsCategoryID)
        {
            try
            {
                ChartShort result = new ChartShort();
                result.AvgMark = 0;
                result.Totals = 0;
                string spName = "News_SelectCountChartAndAverage";
                SqlParameter[] spPara = new SqlParameter[3];
                spPara[0] = new SqlParameter("@dateFrom", dtFrom);
                spPara[1] = new SqlParameter("@dateTo", dtTo);
                spPara[2] = new SqlParameter("@categoryID", newsCategoryID);
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                result.Totals = Convert.ToInt64(ds.Tables[0].Rows[0]["Total"] ?? 0);
                                result.AvgMark = Convert.ToInt32(ds.Tables[0].Rows[0]["AvgMark"] ?? 0);
                            }
                        }
                    }
                }
                return result;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        public static ChartShort GetTotalAndAvgCountry(DateTime dtFrom, DateTime dtTo, int newsCategoryID, int countryID)
        {
            try
            {
                ChartShort result = new ChartShort();
                result.AvgMark = 0;
                result.Totals = 0;
                string spName = "News_SelectCountChartAndAverageCountry";
                SqlParameter[] spPara = new SqlParameter[4];
                spPara[0] = new SqlParameter("@dateFrom", dtFrom);
                spPara[1] = new SqlParameter("@dateTo", dtTo);
                spPara[2] = new SqlParameter("@categoryID", newsCategoryID);
                spPara[3] = new SqlParameter("@countryID", countryID);
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                result.Totals = Convert.ToInt64(ds.Tables[0].Rows[0]["Total"] ?? 0);
                                result.AvgMark = Convert.ToInt32(ds.Tables[0].Rows[0]["AvgMark"] ?? 0);
                            }
                        }
                    }
                }
                return result;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        public static ChartShort GetTotalAndAvgRegion(DateTime dtFrom, DateTime dtTo, int newsCategoryID, int regionID)
        {
            try
            {
                ChartShort result = new ChartShort();
                result.AvgMark = 0;
                result.Totals = 0;
                result.MediaCount = 0;
                string spName = "News_SelectCountChartAndAverageRegion";
                SqlParameter[] spPara = new SqlParameter[4];
                spPara[0] = new SqlParameter("@dateFrom", dtFrom);
                spPara[1] = new SqlParameter("@dateTo", dtTo);
                spPara[2] = new SqlParameter("@categoryID", newsCategoryID);
                spPara[3] = new SqlParameter("@regionID", regionID);
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                result.Totals = Convert.ToInt64(ds.Tables[0].Rows[0]["Total"] ?? 0);
                                result.AvgMark = Convert.ToInt32(ds.Tables[0].Rows[0]["AvgMark"] ?? 0);
                                result.MediaCount = Convert.ToInt32(ds.Tables[0].Rows[0]["MediaCount"] ?? 0);
                            }
                        }
                    }
                }
                return result;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }
    }
}